package com.yeping;

import org.apache.poi.xwpf.usermodel.*;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.*;

import java.io.FileOutputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class MysqlTest {
    //获得驱动
    private static String DRIVER = "com.mysql.cj.jdbc.Driver";
    //获得url
    private static String URL = "jdbc:mysql://localhost:3306/forum?characterEncoding=utf-8&serverTimezone=Asia/Shanghai";
    //获得连接数据库的用户名
    private static String USER = "root";
    //获得连接数据库的密码
    private static String PASS = "3333";


    //需要生成的表结构
    private static String[] TABLE_NAMES = {"article","category","comment","tag","user"};

    private static String OUTPUT_PATH = "D:\\oneDriver\\OneDrive\\桌面\\毕业设计\\sql表格\\tables.doc";

    private static String[] HEADER = {"字段名", "类型", "长度", "是否必须", "字段注释"};
    static {
        try {
            //初始化JDBC驱动并让驱动加载到jvm中
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        Connection conn = null;
        try {
            //连接数据库

        /*
         * 设置可获取REMARK备注信息
        Properties props =new Properties();
        props.put("remarksReporting","true");
        props.put("user", USER);
        props.put("password", PASS);
        conn =DriverManager.getConnection(URL,props);*/

            conn = DriverManager.getConnection(URL, USER, PASS);
            conn.setAutoCommit(true);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    //关闭连接
    public static void close(Object o) {
        if (o == null) {
            return;
        }
        if (o instanceof ResultSet) {
            try {
                ((ResultSet) o).close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else if (o instanceof Statement) {
            try {
                ((Statement) o).close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else if (o instanceof Connection) {
            Connection c = (Connection) o;
            try {
                if (!c.isClosed()) {
                    c.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    public static void close(ResultSet rs, Statement stmt,
                             Connection conn) {
        close(rs);
        close(stmt);
        close(conn);
    }

    public static void close(ResultSet rs,
                             Connection conn) {
        close(rs);
        close(conn);
    }
    /**
     * @throws
     * @Description: 获取表中列值信息
     * @author: chenzw
     * @CreateTime: 2014-1-27 下午2:55:56
     */
    public static List<String[]> printColumnsInfo(String tableName)  throws  Exception{
        List<String[]> res = new ArrayList<>();
        Connection conn = getConnection();
        ResultSet rs = null;

        try {
            /**
             * 设置连接属性,使得可获取到列的REMARK(备注)
             */
//   ((OracleConnection)conn).setRemarksReporting(true);
            DatabaseMetaData dbmd = conn.getMetaData();
            /**
             * 获取可在指定类别中使用的表列的描述。
             * 方法原型:ResultSet getColumns(String catalog,String schemaPattern,String tableNamePattern,String columnNamePattern)
             * catalog - 表所在的类别名称;""表示获取没有类别的列,null表示获取所有类别的列。
             * schema - 表所在的模式名称(oracle中对应于Tablespace);""表示获取没有模式的列,null标识获取所有模式的列; 可包含单字符通配符("_"),或多字符通配符("%");
             * tableNamePattern - 表名称;可包含单字符通配符("_"),或多字符通配符("%");
             * columnNamePattern - 列名称; ""表示获取列名为""的列(当然获取不到);null表示获取所有的列;可包含单字符通配符("_"),或多字符通配符("%");
             */
            rs = dbmd.getTables(null, null, tableName, new String[]{"TABLE", "VIEW"});

            //创建表格
            rs.next();
            res.add(new String[]{rs.getString("REMARKS"), tableName});

            rs = dbmd.getColumns(null, null, tableName, null);

            while (rs.next()) {
                String columnName = rs.getString("COLUMN_NAME");  //列名
                String dataTypeName = rs.getString("TYPE_NAME");  //java.sql.Types类型名称(列类型名称)
                String columnSize = String.valueOf(rs.getInt("COLUMN_SIZE"));  //列大小
                /**
                 *  0 (columnNoNulls) - 该列不允许为空
                 *  1 (columnNullable) - 该列允许为空
                 *  2 (columnNullableUnknown) - 不确定该列是否为空
                 */
                String nullAble = rs.getInt("NULLABLE") == 1 ? "否" : "是";  //是否允许为null
                String remarks = rs.getString("REMARKS");  //列描述
                res.add(new String[]{columnName, dataTypeName, columnSize, nullAble, remarks});





            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return res;
    }
    /**
     * @Description: 跨列合并
     * table要合并单元格的表格
     * row要合并哪一行的单元格
     * fromCell开始合并的单元格
     * toCell合并到哪一个单元格
     */
    public static void mergeCellsHorizontal (XWPFTable table,int row, int fromCell, int toCell){
        for (int cellIndex = fromCell; cellIndex <= toCell; cellIndex++) {
            XWPFTableCell cell = table.getRow(row).getCell(cellIndex);
            if (cellIndex == fromCell) {
                // The first merged cell is set with RESTART merge value
                cell.getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.RESTART);
            } else {
                // Cells which join (merge) the first one, are set with CONTINUE
                cell.getCTTc().addNewTcPr().addNewHMerge().setVal(STMerge.CONTINUE);
            }
        }
    }

    public static void writeTable (XWPFDocument document, List < String[]>datas){
        XWPFTable table = document.createTable(datas.size() + 1, 5);
        mergeCellsHorizontal(table, 0, 1, 4);

        //第一行
        XWPFTableRow row0 = table.getRow(0);
        XWPFTableCell cell00 = row0.getCell(0);
        cell00.setText(datas.get(0)[0]);
        cell00.getCTTc().addNewTcPr().addNewVAlign().setVal(STVerticalJc.CENTER);
        cell00.getCTTc().getPList().get(0).addNewPPr().addNewJc().setVal(STJc.CENTER);

        XWPFTableCell cell01 = row0.getCell(1);
        cell01.setText(datas.get(0)[1]);
        cell01.getCTTc().addNewTcPr().addNewVAlign().setVal(STVerticalJc.CENTER);
        cell01.getCTTc().getPList().get(0).addNewPPr().addNewJc().setVal(STJc.CENTER);


        //第二行，表头
        XWPFTableRow row1 = table.getRow(1);
        for (int i = 0; i < HEADER.length; i++) {
            XWPFTableCell cell = row1.getCell(i);
            cell.setText(HEADER[i]);
            cell.getCTTc().addNewTcPr().addNewVAlign().setVal(STVerticalJc.CENTER);
            cell.getCTTc().getPList().get(0).addNewPPr().addNewJc().setVal(STJc.CENTER);
        }

        //数据行
        for (int i = 1; i < datas.size(); i++) {
            XWPFTableRow row = table.getRow(i + 1);
            String[] data = datas.get(i);
            for (int j = 0; j < data.length; j++) {
                XWPFTableCell cell = row.getCell(j);
                cell.setText(data[j]);
                cell.getCTTc().addNewTcPr().addNewVAlign().setVal(STVerticalJc.CENTER);
                cell.getCTTc().getPList().get(0).addNewPPr().addNewJc().setVal(STJc.CENTER);
            }
        }
        // 添加标题
        XWPFParagraph titleParagraph = document.createParagraph();
        // 设置段落居中
        titleParagraph.setAlignment(ParagraphAlignment.CENTER);
        XWPFRun titleRun = titleParagraph.createRun();
        titleRun.setText("分割");
        titleRun.setFontSize(20);
        titleRun.setFontFamily("宋体");
        titleRun.setBold(true);
    }

    public static void main (String[]args) throws Exception {
        System.out.println("开始获取表的信息......");
        XWPFDocument document = new XWPFDocument();
        for (String tableName : TABLE_NAMES) {
            writeTable(document, printColumnsInfo(tableName)); //获取表中列值信息
        }
        document.write(new FileOutputStream(OUTPUT_PATH));
        System.out.println("获取表信息成功......");
    }
}